package com.canin.dao;

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.canin.model.Dog;

public class DogDAO
{
	private Connection conn = null;

	public DogDAO(Connection conn)
	{
		this.conn = conn;
	}

	public void createNewDogStatic( int memberId, Dog dog ) throws Exception
	{
		// dog_static
		String sql = "insert into dog_static( member_id, breed_id, dog_name, sex, year_ob, month_ob ) " +
				"value( ?,?,?,?,?,? )";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, memberId);
		ps.setInt(2, Integer.parseInt(dog.getBreedNameList()));
		ps.setString(3, dog.getDogName());
		ps.setString(4, dog.getGenders());
		ps.setInt(5, Integer.parseInt( dog.getYears() ));
		ps.setInt(6, Integer.parseInt( dog.getMonths() ));
		
		ps.executeUpdate();
		ps.close();
	}
	
	public ArrayList<Dog> getDogNameList( int memberId ) throws Exception
	{
		ArrayList<Dog> dogNameList = new ArrayList<Dog>();

		String sql = "SELECT * FROM dog_static where member_id=?;";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, memberId);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			Dog dog = new Dog();
			dog.setDogStaticId( rs.getInt( "ID" ) );
			dog.setDogName( rs.getString( "DOG_NAME" ) );
			
			dogNameList.add( dog );
		}

		rs.close();
		ps.close();

		return dogNameList;
	}
	
	public Dog getDogStaticById( int memberId, int dogStaticId ) throws Exception
	{
		Dog dog = new Dog();
		
		String sql = "SELECT * FROM dog_static where member_id=? and id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, memberId);
		ps.setInt(2, dogStaticId);

		ResultSet rs = ps.executeQuery();

		if (rs.next())
		{
			dog.setDogStaticId( rs.getInt( "ID" ) );
			dog.setMemberId( rs.getInt( "member_id" ) );
			dog.setBreedId( rs.getInt( "breed_id" ) );
			dog.setDogName( rs.getString( "dog_name" ) );
			dog.setSex( rs.getString( "sex" ) );
			dog.setYearOfBirth( rs.getInt("year_ob"));
			dog.setMonthOfBirth( rs.getInt("month_ob"));
		}

		rs.close();
		ps.close();
		
		return dog;
	}
	
	/**
	 * Get the lasted one
	 */
	public Dog getDogDynamicById( Dog dog ) throws Exception
	{
//		select * from DOG_DYNAMIC  where dog_static_id=12 and UPDATED=(select max(UPDATED) from DOG_DYNAMIC where dog_static_id=12);
//		String sql = "select *, max(dd.updated) from ( select * from DOG_DYNAMIC dd WHERE dd.dog_static_id=? ) as dd";
		String sql = "select * from DOG_DYNAMIC  where dog_static_id=? " +
				"and UPDATED=(select max(UPDATED) from DOG_DYNAMIC where dog_static_id=?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, dog.getDogStaticId());
		ps.setInt(2, dog.getDogStaticId());

		ResultSet rs = ps.executeQuery();

		if (rs.next())
		{
			dog.setDogDynamicId(rs.getInt( "ID" ));
			dog.setDogWeight(rs.getInt("dog_weight"));
			dog.setDogCoat(rs.getInt("dog_coat"));
			dog.setDogMane(rs.getInt("dog_mane"));
			dog.setPregnantWeeks(rs.getInt( "pregnant_weeks" ));
			dog.setLactateWeeks(rs.getInt("lactate_weeks"));
			dog.setActivityLevel(rs.getInt("activity_level"));
			dog.setAllergyToBeef(rs.getInt("allergy_to_beef"));
			dog.setAllergyToPork(rs.getInt("allergy_to_pork"));
			dog.setAllergyToChicken(rs.getInt("allergy_to_chicken"));
			dog.setAllergyToFish(rs.getInt("allergy_to_fish"));
			dog.setAllergyToMilk(rs.getInt("allergy_to_milk"));
			dog.setAllergyToWhey(rs.getInt("allergy_to_whey"));
			dog.setAllergyToEgg(rs.getInt("allergy_to_egg"));
			dog.setAllergyToSoybean(rs.getInt("allergy_to_soybean"));
			dog.setAllergyToCorn(rs.getInt("allergy_to_corn"));
			dog.setUpdatedDate(rs.getDate("updated"));
		}

		rs.close();
		ps.close();
		
		return dog;
	}
	
	public Dog getDogStaticbyName( int memberId, String dogName ) throws Exception
	{
		Dog dog = new Dog();
		
		String sql = "SELECT * FROM dog_static where member_id=? and dog_name=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, memberId);
		ps.setString(2, dogName);

		ResultSet rs = ps.executeQuery();

		if (rs.next())
		{
			dog.setDogStaticId( rs.getInt( "ID" ) );
			dog.setMemberId( rs.getInt( "member_id" ) );
			dog.setBreedId( rs.getInt( "breed_id" ) );
			dog.setDogName( rs.getString( "dog_name" ) );
			dog.setSex( rs.getString( "sex" ) );
			dog.setYearOfBirth( rs.getInt("year_ob"));
			dog.setMonthOfBirth( rs.getInt("month_ob"));
		}

		rs.close();
		ps.close();
		
		return dog;
	}

	public void createNewDogDynamic( Dog dog ) throws Exception
	{
//		java.util.Date dt = new java.util.Date();
//		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//		String currentTime = sdf.format(dt);
		
		// dog_dynamic
		String sql = "insert into dog_dynamic( dog_static_id, dog_weight, dog_coat, dog_mane, pregnant_weeks," +
				" lactate_weeks, activity_level," +
				" allergy_to_beef, allergy_to_pork, allergy_to_chicken, allergy_to_fish, allergy_to_milk," +
				" allergy_to_whey, allergy_to_egg, allergy_to_soybean, allergy_to_corn, updated, image ) " +
				"value( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, dog.getDogStaticId());
		ps.setInt(2, dog.getDogWeight());
		ps.setInt(3, Integer.parseInt(dog.getCoats()));
		ps.setInt(4, Integer.parseInt(dog.getManeFlag()));
		
		if ( dog.getPregnantWeekList() != null
			&& !"-1".equals(dog.getPregnantWeekList()) )
		{
			ps.setInt(5, Integer.parseInt(dog.getPregnantWeekList()));
		}
		else{
			ps.setInt(5, 0);
		}
			
		if ( dog.getLactateWeekList() != null
			&& !"-1".equals(dog.getLactateWeekList()) ){
			ps.setInt(6, Integer.parseInt(dog.getLactateWeekList()));
		}
		else{
			ps.setInt(6, 0);
		}
		
		ps.setInt(7, Integer.parseInt(dog.getActivityLevelList()));
		ps.setInt(8, Integer.parseInt(dog.getAllergyToBeefFlag()));
		ps.setInt(9, Integer.parseInt(dog.getAllergyToPorkFlag()));
		ps.setInt(10, Integer.parseInt(dog.getAllergyToChickenFlag()));
		ps.setInt(11, Integer.parseInt(dog.getAllergyToFishFlag()));
		ps.setInt(12, Integer.parseInt(dog.getAllergyToMilkFlag()));
		ps.setInt(13, Integer.parseInt(dog.getAllergyToWheyFlag()));
		ps.setInt(14, Integer.parseInt(dog.getAllergyToEggFlag()));
		ps.setInt(15, Integer.parseInt(dog.getAllergyToSoybeanFlag()));
		ps.setInt(16, Integer.parseInt(dog.getAllergyToCornFlag()));
		
//		Date date = new Date(System.currentTimeMillis());
//		ps.setDate(17, date);
		ps.setTimestamp(17, new Timestamp(System.currentTimeMillis()));
		
		ps.setBlob(18, new Blob()
		{
			
			@Override
			public void truncate(long len) throws SQLException
			{
				// TODO Auto-generated method stub
				
			}
			
			@Override
			public int setBytes(long pos, byte[] bytes, int offset, int len)
					throws SQLException
			{
				// TODO Auto-generated method stub
				return 0;
			}
			
			@Override
			public int setBytes(long pos, byte[] bytes) throws SQLException
			{
				// TODO Auto-generated method stub
				return 0;
			}
			
			@Override
			public OutputStream setBinaryStream(long pos) throws SQLException
			{
				// TODO Auto-generated method stub
				return null;
			}
			
			@Override
			public long position(Blob pattern, long start) throws SQLException
			{
				// TODO Auto-generated method stub
				return 0;
			}
			
			@Override
			public long position(byte[] pattern, long start) throws SQLException
			{
				// TODO Auto-generated method stub
				return 0;
			}
			
			@Override
			public long length() throws SQLException
			{
				// TODO Auto-generated method stub
				return 0;
			}
			
			@Override
			public byte[] getBytes(long pos, int length) throws SQLException
			{
				// TODO Auto-generated method stub
				return null;
			}
			
			@Override
			public InputStream getBinaryStream(long pos, long length)
					throws SQLException
			{
				// TODO Auto-generated method stub
				return null;
			}
			
			@Override
			public InputStream getBinaryStream() throws SQLException
			{
				// TODO Auto-generated method stub
				return null;
			}
			
			@Override
			public void free() throws SQLException
			{
				// TODO Auto-generated method stub
				
			}
		});
		
		ps.executeUpdate();
		ps.close();
	}
	
}
